

**note - we will use a combination of personnel files and APFT training records. 

use pid_pde date_snapshot e1apftr e1apftsc e1ymapft using "$data_in/Personnel", clear
rename e1* *

//only observe year and month of APFT so set to the 15th of each month//
gen date_apft = date(ymapft, "YM") + 14
format date_apft %d

gen apftpass = "True" if apftr == "P" & !mi(date_apft)
replace apftpass = "False" if apftr == "F"
rename apftsc scoretotal
keep pid_pde date_apft apftpass scoretotal
drop if mi(date_apft)
drop if date_apft < d(01jan2007)
duplicates drop
destring scoretotal, replace

tempfile apft
save `apft'

//need to format the date column appropriately before appending
use pid_pde date_apft apftpass scoretotal using "$data_in/APFT", clear
replace date_apft = dofc(date_apft)
format date_apft %d
append using `apft'

//only keep one observation for the same date
bys pid_pde date_apft (scoretotal): keep if _n == _N


gen pass = apftpass == "True"
keep pid date_apft pass scoretotal 

bys pid_pde (date_apft): gen n = _n
summ n 
local max_r `r(max)'
reshape wide date_apft pass scoretotal, i(pid_pde) j(n)
merge 1:m pid_pde using "$data_out/provider_propensity", keep(2 3) nogen

keep if officer == 0 & service == "A"




gen apft_annual = 0
gen pass = 0
gen score = 0
forval i = 1/`max_r' {
qui replace apft_annual = 1 if inrange(date_apft`i', encdate, encdate + 365) & date_apft`i' <.
qui replace pass = pass`i' if inrange(date_apft`i', encdate, encdate + 365) & date_apft`i' <. & pass == 0
qui replace score = scoretotal`i' if inrange(date_apft`i', encdate, encdate + 365) & date_apft`i' <. & scoretotal`i' >= score
}

//Z-score//
 summ score if apft_annual == 1
 gen z_score = (score - `r(mean)')/`r(sd)' if apft_annual == 1
replace pass = . if apft_annual == 0

save "$data_out/APFT", replace

